create procedure [dbo].[BAEOrderProductGetAllNotInCategory] @OrderCategoryID as
int
AS
    INSERT INTO OrderProduct
    (IsSuperProduct, ProductCode)
        SELECT 0 AS IsSuperProduct, PRODUCT_CODE
        FROM Product
        WHERE PROD_TYPE = 'SALES' AND STATUS = 'A' AND PRODUCT_CODE COLLATE database_default NOT IN
        (SELECT ProductCode FROM OrderProduct WHERE ProductCode IS NOT NULL);

    (
        SELECT op.OrderProductID, imisp.TITLE COLLATE database_default AS Title, op.IsSuperProduct, imisp.PRODUCT_CODE COLLATE database_default AS ProductCode, WEB_OPTION AS SellOnWeb--op.SellOnWeb
        FROM OrderProduct op
        INNER JOIN Product imisp ON op.ProductCode COLLATE database_default = imisp.PRODUCT_CODE COLLATE database_default
        WHERE op.IsSuperProduct = 0 AND imisp.PROD_TYPE = 'SALES' AND op.OrderProductID NOT IN (SELECT OrderProductID FROM OrderProductCategoryLookup WHERE OrderCategoryID = @OrderCategoryID)
            AND op.ProductCode NOT IN (SELECT ProductCode FROM OrderSuperProductChildProduct)
        UNION
        SELECT op.OrderProductID, op.Title COLLATE database_default AS Title, op.IsSuperProduct, op.ProductCode, op.SellOnWeb
        FROM OrderProduct op
        WHERE op.IsSuperProduct = 1 AND op.OrderProductID NOT IN (SELECT OrderProductID FROM OrderProductCategoryLookup WHERE OrderCategoryID = @OrderCategoryID)
    )
    ORDER BY Title

GO